﻿/*
=============================================================================
Author:魏宝辉通过CodeSmithe生成
CreatedTime:2012-9-9
Description:CustomerList三层结构数据访问层
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using FTchina.Models;//引入模型层
namespace FTchina.DAL
{
	public static partial class CustomerListService
	{
		#region 增加一条数据
		public static int AddCustomerList(CustomerList customerList)
		{
			string sql =
			"INSERT INTO CustomerList(CustomerName,CustormeSex,CustomerTel0,CustomerTel1,CustomerTel2,Customerlandline0,Customerlandline1,Customerlandline2,CustomerWork,CustomerAddress0,CustomerMeetAddress,Memo,CustomerGroupID,Callcount,MeetCount,LastCallDate,LastMeetDate) "+
			"VALUES (@CustomerName,@CustormeSex,@CustomerTel0,@CustomerTel1,@CustomerTel2,@Customerlandline0,@Customerlandline1,@Customerlandline2,@CustomerWork,@CustomerAddress0,@CustomerMeetAddress,@Memo,@CustomerGroupID,@Callcount,@MeetCount,@LastCallDate,@LastMeetDate)";
			
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
						
					new SQLiteParameter("@CustomerName",customerList.CustomerName),
						
					new SQLiteParameter("@CustormeSex",customerList.CustormeSex),
						
					new SQLiteParameter("@CustomerTel0",customerList.CustomerTel0),
						
					new SQLiteParameter("@CustomerTel1",customerList.CustomerTel1),
						
					new SQLiteParameter("@CustomerTel2",customerList.CustomerTel2),
						
					new SQLiteParameter("@Customerlandline0",customerList.Customerlandline0),
						
					new SQLiteParameter("@Customerlandline1",customerList.Customerlandline1),
						
					new SQLiteParameter("@Customerlandline2",customerList.Customerlandline2),
						
					new SQLiteParameter("@CustomerWork",customerList.CustomerWork),
						
					new SQLiteParameter("@CustomerAddress0",customerList.CustomerAddress0),
						
					new SQLiteParameter("@CustomerMeetAddress",customerList.CustomerMeetAddress),
						
					new SQLiteParameter("@Memo",customerList.Memo),
						
					new SQLiteParameter("@CustomerGroupID",customerList.CustomerGroupID),
						
					new SQLiteParameter("@Callcount",customerList.Callcount),
						
					new SQLiteParameter("@MeetCount",customerList.MeetCount),
						
					new SQLiteParameter("@LastCallDate",customerList.LastCallDate),
						
					new SQLiteParameter("@LastMeetDate",customerList.LastMeetDate)
				};
				int newId = DBHelper.ExecuteCommand(sql, para);
				return newId;
			 
		}
		#endregion
		#region 删除数据以主键，标识列，唯一列构建的删除
			
		public static int DeleteCustomerListById(long id)	
		{
			string sql="DELETE From  CustomerList  Where id=@id";
			 SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id", id)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}		
		
		#endregion
		#region 修改数据
		//考虑标识列与主键不一致情形
		//以标识列修改实体
		public static int ModifyCustomerListByid(CustomerList customerList)
		{
		string sql ="UPDATE "+"CustomerList"+" set "+
					"CustomerName=@CustomerName, "+
					"CustormeSex=@CustormeSex, "+
					"CustomerTel0=@CustomerTel0, "+
					"CustomerTel1=@CustomerTel1, "+
					"CustomerTel2=@CustomerTel2, "+
					"Customerlandline0=@Customerlandline0, "+
					"Customerlandline1=@Customerlandline1, "+
					"Customerlandline2=@Customerlandline2, "+
					"CustomerWork=@CustomerWork, "+
					"CustomerAddress0=@CustomerAddress0, "+
					"CustomerMeetAddress=@CustomerMeetAddress, "+
					"Memo=@Memo, "+
					"CustomerGroupID=@CustomerGroupID, "+
					"Callcount=@Callcount, "+
					"MeetCount=@MeetCount, "+
					"LastCallDate=@LastCallDate, "+
					"LastMeetDate=@LastMeetDate "+ 
				" Where id=@id";
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id",customerList.Id),
					new SQLiteParameter("@CustomerName",customerList.CustomerName),
					new SQLiteParameter("@CustormeSex",customerList.CustormeSex),
					new SQLiteParameter("@CustomerTel0",customerList.CustomerTel0),
					new SQLiteParameter("@CustomerTel1",customerList.CustomerTel1),
					new SQLiteParameter("@CustomerTel2",customerList.CustomerTel2),
					new SQLiteParameter("@Customerlandline0",customerList.Customerlandline0),
					new SQLiteParameter("@Customerlandline1",customerList.Customerlandline1),
					new SQLiteParameter("@Customerlandline2",customerList.Customerlandline2),
					new SQLiteParameter("@CustomerWork",customerList.CustomerWork),
					new SQLiteParameter("@CustomerAddress0",customerList.CustomerAddress0),
					new SQLiteParameter("@CustomerMeetAddress",customerList.CustomerMeetAddress),
					new SQLiteParameter("@Memo",customerList.Memo),
					new SQLiteParameter("@CustomerGroupID",customerList.CustomerGroupID),
					new SQLiteParameter("@Callcount",customerList.Callcount),
					new SQLiteParameter("@MeetCount",customerList.MeetCount),
					new SQLiteParameter("@LastCallDate",customerList.LastCallDate),
					new SQLiteParameter("@LastMeetDate",customerList.LastMeetDate)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}					
		#endregion
		#region 查询数据
		public static IList<CustomerList> GetAllCustomerLists()
		{
			string sqlAll = "SELECT * FROM CustomerList";
			return GetCustomerListsBySql(sqlAll);
		}
		//以标识列获得对象
		public static CustomerList GetCustomerListByid(object id)
		{
			string sql = "SELECT * FROM "+"CustomerList"+" WHERE id = @id";
			
            using (SQLiteDataReader reader = DBHelper.GetReader(sql, new SQLiteParameter("@id", id)))
			{
				 
				if (reader.Read())
				{
					CustomerList customerList = new CustomerList();
					customerList.Id=Convert.ToInt32(reader["id"]);
					customerList.CustomerName=Convert.ToString(reader["CustomerName"]);
					customerList.CustormeSex=Convert.ToString(reader["CustormeSex"]);
					customerList.CustomerTel0=Convert.ToString(reader["CustomerTel0"]);
					customerList.CustomerTel1=Convert.ToString(reader["CustomerTel1"]);
					customerList.CustomerTel2=Convert.ToString(reader["CustomerTel2"]);
					customerList.Customerlandline0=Convert.ToString(reader["Customerlandline0"]);
					customerList.Customerlandline1=Convert.ToString(reader["Customerlandline1"]);
					customerList.Customerlandline2=Convert.ToString(reader["Customerlandline2"]);
					customerList.CustomerWork=Convert.ToString(reader["CustomerWork"]);
					customerList.CustomerAddress0=Convert.ToString(reader["CustomerAddress0"]);
					customerList.CustomerMeetAddress=Convert.ToString(reader["CustomerMeetAddress"]);
					customerList.Memo=Convert.ToString(reader["Memo"]);
					customerList.CustomerGroupID=Convert.ToInt32(reader["CustomerGroupID"]);
					customerList.Callcount=Convert.ToInt32(reader["Callcount"]);
					customerList.MeetCount=Convert.ToInt32(reader["MeetCount"]);
					customerList.LastCallDate=Convert.ToDateTime(reader["LastCallDate"]);
					customerList.LastMeetDate=Convert.ToDateTime(reader["LastMeetDate"]);
					reader.Close();
					
					return customerList;
					}
				else
				{
					reader.Close();
					return null;
				}
			}
			 
		}
					
		//私有方法供构建好的SQL语句调用
		private static IList<CustomerList> GetCustomerListsBySql(string safeSql)
		{
			List<CustomerList> list = new List<CustomerList>();
			 
            using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					CustomerList customerList = new CustomerList();
					customerList.Id=Convert.ToInt32(row["id"]);
					customerList.CustomerName=Convert.ToString(row["CustomerName"]);
					customerList.CustormeSex=Convert.ToString(row["CustormeSex"]);
					customerList.CustomerTel0=Convert.ToString(row["CustomerTel0"]);
					customerList.CustomerTel1=Convert.ToString(row["CustomerTel1"]);
					customerList.CustomerTel2=Convert.ToString(row["CustomerTel2"]);
					customerList.Customerlandline0=Convert.ToString(row["Customerlandline0"]);
					customerList.Customerlandline1=Convert.ToString(row["Customerlandline1"]);
					customerList.Customerlandline2=Convert.ToString(row["Customerlandline2"]);
					customerList.CustomerWork=Convert.ToString(row["CustomerWork"]);
					customerList.CustomerAddress0=Convert.ToString(row["CustomerAddress0"]);
					customerList.CustomerMeetAddress=Convert.ToString(row["CustomerMeetAddress"]);
					customerList.Memo=Convert.ToString(row["Memo"]);
					customerList.CustomerGroupID=Convert.ToInt32(row["CustomerGroupID"]);
					customerList.Callcount=Convert.ToInt32(row["Callcount"]);
					customerList.MeetCount=Convert.ToInt32(row["MeetCount"]);
					customerList.LastCallDate=Convert.ToDateTime(row["LastCallDate"]);
					customerList.LastMeetDate=Convert.ToDateTime(row["LastMeetDate"]);
					list.Add(customerList);
				}
				return list;
			}
             
		}
		private static IList<CustomerList> GetCustomerListsBySql( string sql, params SQLiteParameter[] values )
		{
			List<CustomerList> list = new List<CustomerList>();
			 
            using (DataTable table = DBHelper.GetDataTable(sql, values))
			{
				foreach (DataRow row in table.Rows)
				{
					CustomerList customerList = new CustomerList();
					customerList.Id=Convert.ToInt32(row["id"]);
					customerList.CustomerName=Convert.ToString(row["CustomerName"]);
					customerList.CustormeSex=Convert.ToString(row["CustormeSex"]);
					customerList.CustomerTel0=Convert.ToString(row["CustomerTel0"]);
					customerList.CustomerTel1=Convert.ToString(row["CustomerTel1"]);
					customerList.CustomerTel2=Convert.ToString(row["CustomerTel2"]);
					customerList.Customerlandline0=Convert.ToString(row["Customerlandline0"]);
					customerList.Customerlandline1=Convert.ToString(row["Customerlandline1"]);
					customerList.Customerlandline2=Convert.ToString(row["Customerlandline2"]);
					customerList.CustomerWork=Convert.ToString(row["CustomerWork"]);
					customerList.CustomerAddress0=Convert.ToString(row["CustomerAddress0"]);
					customerList.CustomerMeetAddress=Convert.ToString(row["CustomerMeetAddress"]);
					customerList.Memo=Convert.ToString(row["Memo"]);
					customerList.CustomerGroupID=Convert.ToInt32(row["CustomerGroupID"]);
					customerList.Callcount=Convert.ToInt32(row["Callcount"]);
					customerList.MeetCount=Convert.ToInt32(row["MeetCount"]);
					customerList.LastCallDate=Convert.ToDateTime(row["LastCallDate"]);
					customerList.LastMeetDate=Convert.ToDateTime(row["LastMeetDate"]);
					list.Add(customerList);
				}
				return list;
			}
            
			 
		}
		#endregion
		 
	}
}

